package com.mai.demo.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.mai.demo.entity.CommonExcel;
import com.mai.demo.entity.domain.Course;
import com.mai.demo.entity.domain.SchoolClass;
import com.mai.demo.entity.domain.Teacher;
import com.mai.demo.mapper.ClassMapper;
import com.mai.demo.mapper.CourseMapper;
import com.mai.demo.mapper.TeacherMapper;
import com.mai.demo.service.ExcelConvertService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

import static cn.hutool.poi.excel.cell.CellUtil.getCellValue;

/**
 * @className: ExcelConvertServiceImpl
 * @author: Wang
 * @date: 2024/12/24 下午8:44
 * @Version: 1.0
 */
@Slf4j
@Service
public class ExcelConvertServiceImpl implements ExcelConvertService {


    @Resource
    private ClassMapper classMapper;

    @Resource
    private CourseMapper courseMapper;

    @Resource
    private TeacherMapper teacherMapper;


    @Override
    public void convert(MultipartFile file, Integer startColumn, Integer endColumn, Integer startRow, Integer endRow, HttpServletResponse response) {
        // 获取当前时间并格式化
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String currentTime = dateFormat.format(new Date());
        // 设置响应头
        setExcelResponseProp(response, "任课数据安排表_" + currentTime);

        List<String> excelData;


        try (InputStream ips = file.getInputStream()) {
            excelData = getExcelData(ips, startColumn, endColumn, startRow, endRow);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        // 创建ExcelWriter对象
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), CommonExcel.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .build();
            List<CommonExcel> writeExelData = getWriteExcelData(excelData);
            // 写入工作表
            WriteSheet writeSheet1 = EasyExcel.writerSheet("lecture2").build();
            excelWriter.write(writeExelData, writeSheet1);

        } catch (IOException e) {
            throw new RuntimeException(e);
        }finally {
            // 完成写入操作并关闭ExcelWriter
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }
    private List<CommonExcel> getWriteExcelDataByDB4TemplateData(List<String> excelDatas) {
        List<CommonExcel> result = new ArrayList<>();

        for (String str : excelDatas) {
            // 1.切割字符串
            String[] parts = str.split(":");
            // 输出切割后的字符串数组
            String courseName = parts[0];
            String className = parts[1];
            String teacherName = parts[2];

            // 获取课程
            String subClass = className.substring(0, 1);
//                List<Course> course= courseMapper.getList(courseName,subClass);

            List<Course> course = courseMapper.selectList(
                    new LambdaQueryWrapper<Course>()
                            .likeRight(Course::getCourName, courseName)
                            .like(Course::getCourName, subClass)
            );

            for (Course course1 : course) {
                // 获取老师
                String firstName = teacherName.substring(0, 1);
                String lastName = teacherName.substring(1);
                Teacher teacher = teacherMapper.selectOne(
                        new LambdaQueryWrapper<Teacher>()
                                .eq(Teacher::getTrFirstName, firstName)
                                .eq(Teacher::getTrName, lastName)
                                .orderByAsc(Teacher::getId)
                                .last("limit 1")
                );
                // 获取班级
                className = className.replaceAll("（", "")
                        .replaceAll("）", "")
                        .replaceAll("\\(","")
                        .replaceAll("\\)","")
                        .replace("级", "");

                if (!StrUtil.contains(className,"年")){
                    StringBuilder stringBuilder = new StringBuilder();
                    char[] charArray = className.toCharArray();
                    for (int i = 0; i < charArray.length; i++) {
                        stringBuilder.append(i== 0 ? charArray[i]+"年": charArray[i]);
                    }
                    className = stringBuilder.toString();
                }

                if (!StrUtil.contains(className,"班")){
                    className = className+"班";
                }

                SchoolClass class1 = classMapper.selectOne(
                        new LambdaQueryWrapper<SchoolClass>()
                                .eq(SchoolClass::getClassName, className)
                );

                //  因为excel教师名字和数据库不一样,相反了,找不到数据
                if (teacher == null) {
                    break;
                }
                if (class1 == null) {
                   break;
                }

                CommonExcel excel = new CommonExcel();
                excel.setCourName(course1.getCourName());
                excel.setCourNum(course1.getCourNum());
                excel.setTrNum(teacher.getTrNum());
                excel.setTrName(teacher.getTrFirstName() + teacher.getTrName());
                excel.setClassNum(class1.getClassNum());
                excel.setClassName(class1.getClassName());
                excel.setCountMax(60);
                excel.setCountMin(30);
                excel.setBeginTime("2024-9-2");
                excel.setEndTime("2025-1-12");
                result.add(excel);
            }
        }
        return result;
    }

    private List<CommonExcel> getWriteExcelData(List<String> excelDatas) {
        List<CommonExcel> result = new ArrayList<>();
        String temp = "";
        try {
            for (String str : excelDatas) {
                // 1.切割字符串
                String[] parts = str.split(":");
                // 输出切割后的字符串数组
                String courseName = parts[0];
                String className = parts[1];
                String teacherName = parts[2];

                temp = teacherName;

                if (!StrUtil.contains(className,"@")) continue;

                String[] classNameSpiltArr = className.split("@");
                String classNum = classNameSpiltArr[0];
                className = classNameSpiltArr[1];

                // 获取课程
                String subClass = className.substring(0, 1);

                List<Course> courseList = courseMapper.selectList(
                        new LambdaQueryWrapper<Course>()
                                .likeRight(Course::getCourName, courseName)
                                .like(Course::getCourName, subClass)
                );

                for (Course course : courseList) {
                    // 这里的数据可能有些问题 换行符后的数据会读取不到
                    // 所以读取的时候会报ArrayIndexOutOfBoundsException continue 跳过
                    if (!StrUtil.contains(teacherName,"#")) break;
                    // 获取老师
                    String trNum ;
                    String firstName ;
                    String lastName ;
                    try {
                        String[] teacherNameSplitArr = teacherName.split("#");
                        trNum = teacherNameSplitArr[0];
                        firstName = teacherNameSplitArr[1].substring(0, 1);
                        lastName = teacherNameSplitArr[1].substring(1);
                    } catch (ArrayIndexOutOfBoundsException e) {
                        continue;
                    }
                    // 获取班级
                    className = className.replaceAll("（", "")
                            .replaceAll("）", "")
                            .replaceAll("\\(","")
                            .replaceAll("\\)","")
                            .replace("级", "");

                    if (!StrUtil.contains(className,"年")){
                        StringBuilder stringBuilder = new StringBuilder();
                        char[] charArray = className.toCharArray();
                        for (int i = 0; i < charArray.length; i++) {
                            stringBuilder.append(i== 0 ? charArray[i]+"年": charArray[i]);
                        }
                        className = stringBuilder.toString();
                    }

                    if (!StrUtil.contains(className,"班")){
                        className = className+"班";
                    }

                    CommonExcel excel = new CommonExcel();
                    excel.setCourName(course.getCourName());
                    excel.setCourNum(course.getCourNum());
                    excel.setTrNum(trNum);
                    excel.setTrName(firstName+ lastName);
                    excel.setClassNum(classNum);
                    excel.setClassName(className);
                    excel.setCountMax(60);
                    excel.setCountMin(30);
                    excel.setBeginTime("2024-9-2");
                    excel.setEndTime("2025-1-12");
                    result.add(excel);
                }
            }
            return result;
        } catch (Exception e) {
            log.info("temp:{}",temp);
            throw new RuntimeException(e);
        }
    }

    private List<String> getExcelData(InputStream in, Integer startColumn, Integer endColumn, Integer startRow, Integer endRow) {
        // 存储数据
        List<String> data = new ArrayList<>();
        // 通过输入流读取指定的Excel文件
        try (Workbook excel = WorkbookFactory.create(in)) {
            for (Sheet sheet : excel) {
                // 获取Sheet页中的最后一行的行号
                int lastRowNum = sheet.getLastRowNum();

                // 保存班级行数据
                Map<Integer, String> firstRowData = new HashMap<>();
                Row firstRow = sheet.getRow(0);
                if (firstRow != null) {
                    int lastCellNum = firstRow.getLastCellNum();
                    for (int j = 0; j < lastCellNum; j++) {
                        Cell cell = firstRow.getCell(j);
                        if (cell != null && cell.getCellType() != CellType.BLANK) {
                            String value = cell.getStringCellValue();
                            firstRowData.put(j, value);
                        }
                    }
                }

                // 调整行遍历范围
                for (int i = Math.max(0, startRow); i <= Math.min(lastRowNum, endRow); i++) {
                    // 获取Sheet页中的行
                    Row titleRow = sheet.getRow(i);

                    if (titleRow != null) {
                        Cell course = titleRow.getCell(0);
                        if (course == null || course.getCellType() != CellType.STRING) {
                           continue;
                        }

                        int lastCellNum = titleRow.getLastCellNum();

                        // 课程名称
                        String key = !StrUtil.isBlankIfStr(course.getStringCellValue()) ? course.getStringCellValue() : "";
                        // 调整列遍历范围
                        for (int j = Math.max(0, startColumn); j <= Math.min(lastCellNum, endColumn); j++) {
                            Cell cell = titleRow.getCell(j);
                            if (cell == null) continue;
                            CellType cellType = cell.getCellType();
                            if (cellType != CellType.STRING) {
                                continue;
                            }
                            // 第二个单元格为空
                            if (titleRow.getCell(1).getCellType() == CellType.BLANK) {
                                break;
                            }

                            // 获取当前列的第一行的值
                            String className = firstRowData.get(j);
                            // 这里处理非空且非数字类型的单元格，并使用第一行的值作为key

                            data.add(key + ':' + className + ':' + cell.getStringCellValue().replaceAll("\n","").replaceAll(" ",""));
                        }
                    }
                }
            }
            return data;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    private void setExcelResponseProp(HttpServletResponse response, String fileName) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String encodedFileName;
        try {
            encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
    }

    /**
     * 处理页
     *
     * @param sourceSheet 源页
     * @param startColumn 源页起始列
     * @param endColumn   源页末列
     * @param startRow    源页起始行
     * @param endRow      源页末行
     * @param targetSheet 目标页
     */
    private void handleSheet(Sheet sourceSheet, Integer startColumn, Integer endColumn, Integer startRow, Integer endRow, Sheet targetSheet) {
        int rowcount = sourceSheet.getPhysicalNumberOfRows();
        // 转换格式要求
        String commonSuffix = "@";
        // 课程转换
        // 课程所在的列数
        Set<Integer> courseConvertCellIndexes = CollUtil.newHashSet(0);
        // 忽略转换所在的行数
        Set<Integer> ignoreCourseConvertRowIndexes = CollUtil.newHashSet(0);
        // 班级转换
        // 班级所在的行数
        Set<Integer> classConvertRowIndexes = CollUtil.newHashSet(0);

        int targetRowCount = 3;
        // 读行
        for (int i = startRow; i <= endRow; i++) {
            Row sourceRow = sourceSheet.getRow(i);
            if (sourceRow == null) continue;
            // 模版默认行不处理
            Row targetRow = targetSheet.createRow(targetRowCount);
//            copyRowHeight(row, workRow); // 复制行高
            // 满足班级转换条件
            if (classConvertRowIndexes.contains(i)) {
                // 读单元格
                for (int k = startColumn; k <= endColumn; k++) {
                    Cell sourceCell = sourceRow.getCell(k);
                    Cell classCodeCell = targetRow.createCell(4);
                    Cell classNameCell = targetRow.createCell(5);

                    // 去除换行符 和空格
                    String cellValue = getCellValue(sourceCell).toString()
                            .replaceAll("\n", "")
                            .replaceAll(" ", "");

                    // 处理excel中班级的命名格式
                    if (StrUtil.contains(cellValue, "（") && StrUtil.contains(cellValue, "）")) {
                        // 小学处理
                        cellValue = cellValue
                                .replaceAll("（", "")
                                .replaceAll("）", "");
                        StringBuilder sb = new StringBuilder();
                        char[] charArray = cellValue.toCharArray();
                        for (int m = 0; m < charArray.length; m++) {
                            sb.append(m == 0 ? charArray[m] + "年" : charArray[m]);
                        }
                        cellValue = sb.toString();
                    }

                    if (StrUtil.contains(cellValue, "级")) {
                        // 初中处理
                        cellValue = cellValue.replaceAll("级", "");
                    }

                    // 忽略空字符串
                    if (StrUtil.isBlank(cellValue)) continue;

                    SchoolClass schoolClass = classMapper.selectOne(
                            new LambdaQueryWrapper<SchoolClass>()
                                    .like(SchoolClass::getClassName, cellValue)
                    );
                    // 忽略未匹配
                    if (schoolClass == null) continue;
                    System.out.println("schoolClass = " + schoolClass);

                    classCodeCell.setCellValue(schoolClass.getClassNum());
                    classNameCell.setCellValue(schoolClass.getClassName());
                    System.out.print("班级转化---");
                    System.out.println("cellValue = " + cellValue);
                }
                continue;
            }

            // 读单元格
            for (int k = startColumn; k <= endColumn; k++) {
                Cell cell = sourceRow.getCell(k);
                Object cellValue = getCellValue(cell);

                // 转换类型只需要考虑字符串类型
                if (!(cellValue instanceof String)) continue;

                cellValue = cellValue.toString()
                        .replaceAll("\n", "");

                // 忽略空字符串
                if (StrUtil.isBlankIfStr(cellValue)) continue;

                // 满足课程转换条件
                if (courseConvertCellIndexes.contains(k) && !ignoreCourseConvertRowIndexes.contains(i)) {
                /*
                  这里的格式转换是最恶心的
                  excel中的课程没有携带班级信息 但数据库里有 并且有上下学期之分
                  除非补充原模版1中的数据 不然将无法进行转换
                  若强行查库 会面临 eq 查不到 like 拿多个的情况
                  补数据是不可能补数据的doge 用 like查多条按id排序 拿第一条
                 */
                    /*
                     *  若硬要补，请按照以下格式要求
                     *
                     *  1. 数据库内若没有匹配的数据,请不要标注，默认即可
                     *     1.1 课程： courNum@courName
                     *     1.2 班级:  classNum@className
                     *     1.3 教师：  trNum@trFirstName+trName
                     *  2. 会有同名的情况发生，请采取随机策略
                     *  3. 重点课程处理 ：
                     *    模版1格式中课程信息请处理成如下格式：
                     *    课程+班级 如：高尔夫（二下） 音乐（四上）
                     * */
                    System.out.print("课程转换---");
                    if (StrUtil.isBlankIfStr(cellValue)) continue;
                    Course course = courseMapper.selectOne(
                            new LambdaQueryWrapper<Course>()
                                    .like(Course::getCourName, cellValue)
                                    .orderByAsc(Course::getId)
                                    .last("limit 1")
                    );
                    if (course == null) continue;
                    Cell courseCodeCell = targetRow.createCell(2);
                    Cell courseNameCell = targetRow.createCell(3);
                    courseCodeCell.setCellValue(course.getCourNum());
                    courseNameCell.setCellValue(course.getCourName());
                    cellValue = course.getCourNum() + commonSuffix + cellValue;
                } else {
                    // 至此 为教师转换的处理
                    System.out.print("教师转换---");
                    String firstName = cellValue.toString().substring(0, 1);
                    String lastName = cellValue.toString().substring(1);
                    //  数据不足的同名处理 fixme 感觉老毕登那过不了关这么搞
                    Teacher teacher = teacherMapper.selectOne(
                            new LambdaQueryWrapper<Teacher>()
                                    .eq(Teacher::getTrFirstName, firstName)
                                    .eq(Teacher::getTrName, lastName)
                                    .orderByAsc(Teacher::getId)
                                    .last("limit 1")
                    );
                    if (teacher != null) {
                        Cell teacherCodeCell = targetRow.createCell(0);
                        Cell teacherNameCell = targetRow.createCell(0);
                        teacherCodeCell.setCellValue(teacher.getTrNum());
                        teacherNameCell.setCellValue(teacher.getTrName());
                        cellValue = teacher.getTrNum() + commonSuffix + cellValue;
                    }
                }
                System.out.println("newCellValue = " + cellValue);
            }
        }
//        copyColumnWidth(sourceSheet, targetSheet); // 复制列宽
    }

    private static void copyCell(Cell sourceCell, Cell destCell) {
        // 避免直接设置单元格类型为公式
        if (sourceCell.getCellType() == CellType.FORMULA) {
            // 如果是公式类型，直接设置公式内容
            destCell.setCellFormula(sourceCell.getCellFormula());
        } else {
            // 对于其他类型的单元格，设置其类型
            destCell.setCellType(sourceCell.getCellType());

            // 根据单元格类型设置相应的值
            switch (sourceCell.getCellType()) {
                case STRING:
                    destCell.setCellValue(sourceCell.getStringCellValue());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(sourceCell)) {
                        destCell.setCellValue(sourceCell.getDateCellValue());
                    } else {
                        destCell.setCellValue(sourceCell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    destCell.setCellValue(sourceCell.getBooleanCellValue());
                    break;
                case BLANK:
                    // 空单元格不需要特殊处理
                    break;
                case ERROR:
                    destCell.setCellErrorValue(sourceCell.getErrorCellValue());
                    break;
                case FORMULA:
                    // 这里已经通过 setCellFormula 处理过了，避免重复处理
                    break;
                default:
                    // 其他情况可以抛出异常或进行日志记录
                    throw new IllegalStateException("Unexpected cell type: " + sourceCell.getCellType());
            }
        }

        // 复制单元格样式
        CellStyle sourceCellStyle = sourceCell.getCellStyle();
        CellStyle destCellStyle = destCell.getSheet().getWorkbook().createCellStyle();
        if (sourceCellStyle != null) {
            destCellStyle.cloneStyleFrom(sourceCellStyle);
            destCell.setCellStyle(destCellStyle);
        }

        // 复制注释（如果有）
        Comment comment = sourceCell.getCellComment();
        if (comment != null) {
            Drawing<?> patriarch = destCell.getSheet().createDrawingPatriarch();
            CreationHelper createHelper = destCell.getSheet().getWorkbook().getCreationHelper();
            ClientAnchor anchor = createHelper.createClientAnchor();
            anchor.setCol1(destCell.getColumnIndex());
            anchor.setRow1(destCell.getRowIndex());
            patriarch.createCellComment(anchor).setString(comment.getString());
        }
    }

    private static void copyRowHeight(Row sourceRow, Row destRow) {
        if (sourceRow != null && destRow != null) {
            destRow.setHeight(sourceRow.getHeight());
        }
    }

    private static void copyColumnWidth(Sheet sourceSheet, Sheet destSheet) {
        for (int i = 0; i < sourceSheet.getPhysicalNumberOfRows(); i++) {
            Row row = sourceSheet.getRow(i);
            if (row != null) {
                for (Cell cell : row) {
                    int columnIndex = cell.getColumnIndex();
                    if (columnIndex >= 0 && columnIndex < destSheet.getRow(0).getPhysicalNumberOfCells()) {
                        destSheet.setColumnWidth(columnIndex, sourceSheet.getColumnWidth(columnIndex));
                    }
                }
            }
        }
    }

}
